#!/bin/bash

#function:create database,create table,insert data,query data
#author:caoyong
#version:1.0
#time:2017-7-1

menu(){
echo "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
echo -e "@\e[5;31;46mdatabase tools \e[0m"
echo -e "@\e[36m1.create database\e[0m"
echo -e "@\e[35m2.create table\e[0m"
echo -e "@\e[33m3.insert data\e[0m"
echo -e "@\e[34m4.query data\e[0m"
echo -e "@\e[32m5.exit\e[0m"
echo "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"

read -p "please input your choice:" option
}

#1.create database
create_db(){
	mysql -uroot -p123123 -e "show databases"
	read -p "please input database's name:" db_name
	mysql -uroot -p123123 <<EOF
	create database $db_name;
	show databases;
	quit
EOF
	echo
}

#2.create table
create_tab(){
	mysql -uroot -p123123 -e "show databases"

        read -p "please input you need use database's name:" db_name1
        
        mysql -uroot -p123123 <<EOF
        use $db_name1;
        show tables;
        quit
EOF
	echo

        read -p "please input a not exists table's name:" tab_name
        mysql -uroot -p123123 <<EOF
        use $db_name1;
	create table IF NOT EXISTS $tab_name(id int(5),name varchar(20),sex varchar(10),address varchar(40));
	desc $tab_name;
	quit
EOF
	echo
}

#3.insert data
insert_data(){
	mysql -uroot -p123123 -e "show databases"
	
	read -p "please input you need use database's name:" db_name2
	
	mysql -uroot -p123123 <<EOF
	use $db_name2;
	show tables;
	quit
EOF
	echo

	read -p "please input a you want to insert data table's name:" tab_name1

	mysql -uroot -p123123 <<EOF
	use $db_name2;
	insert into $tab_name1(id,name,sex,address) values(1,'Tom','male','hunan');
	insert into $tab_name1(id,name,sex,address) values(2,'Nana','female','beijing');
	insert into $tab_name1(id,name,sex,address) values(3,'Joe','male','hubei');
	quit
EOF
	echo
}


#4.query data
query_data(){
	mysql -uroot -p123123 -e "show databases"

        read -p "please input you need use database's name:" db_name3

        mysql -uroot -p123123 <<EOF
        use $db_name3;
        show tables;
	quit
EOF
	echo
	
	read -p "please input a table's name:" tab_name2
        
	mysql -uroot -p123123 <<EOF
        use $db_name3;
        select * from $tab_name2;
        quit
EOF
	echo
}

#main
main(){
	while :
	do
	menu
	case $option in
	1)
	create_db
	;;
	2)
	create_tab
	;;
	3)
	insert_data
	;;
	4)
	query_data
	;;
	5)
	exit
	;;
	*)
	exit
	;;
	esac
	done
}
main;
